package com.junzhiit.nengji.shop.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.UserBO;
import com.junzhiit.nengji.shop.bo.UserRelationshipsBO;
import com.junzhiit.nengji.shop.bo.UserTakeCashBO;
import com.junzhiit.nengji.shop.dao.UserDAO;
import com.junzhiit.nengji.shop.model.User;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.MemberLevelCode;
import com.junzhiit.nengji.shop.model.enums.SortStatus;
import com.junzhiit.nengji.shop.model.enums.Status;

@Repository
public class UserDAOImpl extends AbstractBaseDAO<User> implements UserDAO{

	
	public List<UserBO> findAll(int pageNumber, int maxPageRow,UserBO userBO) {
		StringBuilder sql = new StringBuilder();
		sql.append("select u.id,u.code,u.full_code,u.parent_id,u.open_id,u.nick_name,u.name");
		sql.append(",u.identity,u.email,u.mobile_phone,u.sex,u.avatar_url,u.language,u.is_shop");
		sql.append(",u.city,u.province,u.country,u.subscribe,u.subscribe_time,u.status,v.identity vip_identity,v.id vip_id");
		sql.append(",IFNULL(uc.current_money,0.00) 'current_money',IFNULL(uc.pile_money,0.00) 'pile_money'");
		sql.append(",IFNULL(uc.pile_sell_money,0.00) 'pile_sell_money',IFNULL(uc.pile_lege_integral,0) 'pile_lege_integral'");
		sql.append(",IFNULL(uc.pile_income_money,0.00) 'pile_income_money',uc.level,u.birthday");
		sql.append(",IFNULL(uc.pile_rebate_money,0.00) 'pile_rebate_money'");
		sql.append(",IFNULL(uc.withdrawal_money,0.00) 'withdrawal_money',IFNULL(uc.lege_integral,0) 'lege_integral'");
		sql.append(" from user u");
		sql.append(" inner join user_config uc on uc.user_id = u.id");
		sql.append(" left join user_vip uv on u.id=uv.user_id and uv.is_delete=? and uv.is_use=?");
		sql.append(" left join vip v on uv.vip_id=v.id");
		sql.append(" where 1=1");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		if(userBO.getSex() != null){
			sql.append(" and u.sex = ?");
			params.setInt(userBO.getSex().intValue());
		}
		if(StringUtils.isNotBlank(userBO.getMobilePhone())){
			sql.append(" and u.mobile_phone like ?");
			params.setString("%"+ userBO.getMobilePhone() +"%");
		}
		if(StringUtils.isNotBlank(userBO.getCode())){
			sql.append(" and u.code = ?");
			params.setString(userBO.getCode());
		}
		if(userBO.getStatus() != null){
			sql.append(" and u.status = ?");
			params.setInt(userBO.getStatus().intValue());
		}
		
		if(StringUtils.isNotBlank(userBO.getName())){
			sql.append(" and u.name like ?");
			params.setString("%"+ userBO.getName()+"%");
		}
		if(StringUtils.isNotBlank(userBO.getNickName())){
			sql.append(" and u.nick_name like ?");
			params.setString("%"+ userBO.getNickName()+"%");
		}
		
		if(userBO.getLevel() != null){
			sql.append(" and uc.level != ?");
			params.setInt(MemberLevelCode.ZERO.toIntValue());
			sql.append(" order by uc.level desc");
		}else{
			sql.append(" order by u.subscribe_time desc");
		}
		return super.queryForPagingList(sql.toString(), UserBO.class, params, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCount(UserBO userBO) {
		StringBuilder sql = new StringBuilder();
		sql.append("select count(u.id)");
		sql.append(" from user u");
		sql.append(" inner join user_config uc on uc.user_id = u.id");
		sql.append(" left join user_vip uv on u.id=uv.user_id and uv.is_delete=? and uv.is_use=?");
		sql.append(" left join vip v on uv.vip_id=v.id");
		sql.append(" where 1=1");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		if(userBO.getSex() != null){
			sql.append(" and u.sex = ?");
			params.setInt(userBO.getSex().intValue());
		}
		if(StringUtils.isNotBlank(userBO.getMobilePhone())){
			sql.append(" and u.mobile_phone like ?");
			params.setString("%"+ userBO.getMobilePhone() +"%");
		}
		if(StringUtils.isNotBlank(userBO.getCode())){
			sql.append(" and u.code = ?");
			params.setString(userBO.getCode());
		}
		if(userBO.getStatus() != null){
			sql.append(" and u.status = ?");
			params.setInt(userBO.getStatus().intValue());
		}
		
		if(StringUtils.isNotBlank(userBO.getName())){
			sql.append(" and u.name like ?");
			params.setString("%"+ userBO.getName()+"%");
		}
		if(StringUtils.isNotBlank(userBO.getNickName())){
			sql.append(" and u.nick_name like ?");
			params.setString("%"+ userBO.getNickName()+"%");
		}
		
		if(userBO.getLevel() != null){
			sql.append(" and uc.level != ?");
			params.setInt(MemberLevelCode.ZERO.toIntValue());
			sql.append(" order by uc.level desc");
		}else{
			sql.append(" order by u.subscribe_time desc");
		}
		return super.queryForInt(sql.toString(),params);
	}

	@Override
	public User checkOpenId(String openId) {
		String sql = "select * from user where open_id =?";
		StatementParameter params = new StatementParameter();
		params.setString(openId);
		return super.query(sql.toString(),User.class,params);
	}
	
	public UserBO getUserById(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select u.id,u.code,u.full_code,u.parent_id,u.is_member_sell,");
		sql.append("u.open_id,u.nick_name,u.name,u.identity,");
		sql.append("u.email,u.mobile_phone,u.birthday,u.sex,u.avatar_url 'head_img_url',");
		sql.append("u.language,u.city,u.province,u.country,u.subscribe,u.subscribe_time,");
		sql.append("u.status,uc.current_money,uc.pile_money,uc.current_integral,uc.withdrawal_money,uc.lege_integral,");
		sql.append("uc.pile_integral,uc.level,uc.qr_code,uc.expire_date,u.is_shop,");
		sql.append("(select count(id) from user_coupons where user_id = ? ) as couponsCount");
		sql.append(" from user u");
		sql.append(" left join user_config uc on uc.user_id = u.id");
		sql.append(" where u.is_delete=? and u.id=? ");
		StatementParameter params = new StatementParameter();
		params.setString(id);
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(id);
		return query(sql.toString(), UserBO.class, params);
	}
	
	public int checkMember(String userId) {
		StatementParameter params = new StatementParameter();
		String sql = "select count(id) from user where id = ? ";
		params.setString(userId);
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public User checkEmail(String email) {
		String sql = "select * from user where is_delete=? and email =?";
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(email);
		return super.query(sql.toString(),User.class,params);
	}

	@Override
	public User checkMobilePhone(String mobilePhone) {
		String sql = "select * from user where is_delete=? and mobile_phone =?";
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(mobilePhone);
		return super.query(sql.toString(),User.class,params);
	}

	@Override
	public UserBO findUserId(String id) {
		StringBuilder sql = new StringBuilder();
		sql.append("select u.id,u.code,u.full_code,u.parent_id,is_member_sell,");
		sql.append("u.open_id,u.nick_name,u.name,u.identity,");
		sql.append("u.email,u.mobile_phone,u.birthday,u.sex,u.avatar_url 'head_img_url',");
		sql.append("u.language,u.city,u.province,u.country,u.subscribe,u.subscribe_time,");
		sql.append("u.status,uc.current_money,uc.pile_money,uc.current_integral,");
		sql.append("uc.pile_integral,uc.level,uc.qr_code,uc.expire_date,uc.withdrawal_money");
		sql.append(" from user u");
		sql.append(" inner join user_config uc on uc.user_id = u.id");
		sql.append(" where u.is_delete=?");
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		if(StringUtils.isNoneBlank(id)){
			sql.append(" and u.id=?");
			params.setString(id);
		}
		return query(sql.toString(), UserBO.class, params);
	}

	@Override
	public User getUserByCode(String code) {
		String sql = "select * from user where code =? and is_delete =?";
		StatementParameter params = new StatementParameter();
		params.setString(code);
		params.setInt(IsDelete.NO.toIntValue());
		return super.query(sql,User.class,params);
	}

	@Override
	public int getCountByUser() {
		String sql="select count(id) from user where is_delete = ?";
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql.toString(),params);
	}

	@Override
	public List<UserRelationshipsBO> getUserRelationships(String id,Integer sortStatus,String params,Integer pageNumber,Integer maxPageRow) {
		StringBuilder sql = new StringBuilder();
		StatementParameter param = new StatementParameter();
		sql.append("SELECT u.*, user.nick_name as nickName, user.avatar_url as headImg "); 
		sql.append("FROM ( "); 
		sql.append("  SELECT u2.u1_id AS id, u2.money comsumeMoney, SUM(u2.money2)  oneComsumeMoney"); 
		sql.append("  , u2.subscribe_time as subscribeTime, COUNT(u2.id) oneLevelCount ");
		sql.append("  FROM ( "); 
		sql.append("    SELECT u2.u1_id, u2.id, u2.money, u2.subscribe_time, SUM(IFNULL(ph.money, 0)) money2 "); 
		sql.append("    FROM ( "); 
		sql.append("      SELECT u1.u1_id, u1.money, u1.subscribe_time, u2.id "); 
		sql.append("      FROM ( "); 
		sql.append("        SELECT * "); 
		sql.append("        FROM ( "); 
		sql.append("          SELECT u.id u1_id, u.subscribe_time, SUM(IFNULL(ph.money, 0)) money "); 
		sql.append("          FROM ( "); 
		sql.append("            SELECT u2.* "); 
		sql.append("            FROM ( "); 
		sql.append("              SELECT * "); 
		sql.append("              FROM `user` "); 
		sql.append("              WHERE id = ?"); 
		param.setString(id);
		sql.append("            ) u1 "); 
		sql.append("            JOIN `user` u2 "); 
		sql.append("            ON u1.id = u2.parent_id "); 
		if(StringUtils.isNoneBlank(params)){
			sql.append("			WHERE concat(IFNULL(u2.name, ''), u2.nick_name,IFNULL(u2.mobile_phone, '')) LIKE CONCAT('%',?,'%')");
			param.setString(params);
		}
		if(sortStatus!=null&&sortStatus == SortStatus.SUBSCRIBETIME.toIntValue()){
			sql.append("        ORDER BY u2.subscribe_time DESC "); //-- 关注时间 
			sql.append("        LIMIT ?, ? "); // -- 关注时间
			param.setInt(pageNumber);
			param.setInt(maxPageRow);
		}
		sql.append("          ) u "); 
		sql.append("          LEFT JOIN pay_history ph "); 
		sql.append("          ON u.id = ph.user_id "); 
		sql.append("          AND ph.type = 0 "); 
		sql.append("         GROUP BY u.id, u.subscribe_time "); 
		sql.append("        ) u1 "); 
		if(sortStatus!=null&&sortStatus==SortStatus.MONEY.toIntValue()){
			sql.append("  ORDER BY money DESC  "); //-- 业绩
			sql.append("  LIMIT ?, ?  "); //-- 业绩
			param.setInt(pageNumber);
			param.setInt(maxPageRow);
		}
		sql.append("      ) u1 "); 
		sql.append("      LEFT JOIN `user` u2 "); 
		sql.append("      ON u1.u1_id = u2.parent_id "); 
		sql.append("    ) u2 "); 
		sql.append("    LEFT JOIN pay_history ph "); 
		sql.append("    ON u2.id = ph.user_id "); 
		sql.append("    AND ph.type = 0 "); 
		sql.append("    GROUP BY u2.u1_id, u2.id, u2.money, u2.subscribe_time "); 
		sql.append("  ) u2 "); 
		sql.append("  GROUP BY u2.u1_id, u2.money, u2.subscribe_time "); 
		if(sortStatus!=null&&sortStatus==SortStatus.USERCOUNT.toIntValue()){
			sql.append("ORDER BY oneLevelCount DESC "); // -- 关注人数
			sql.append("LIMIT ?, ? ");// -- 关注人数
			param.setInt(pageNumber);
			param.setInt(maxPageRow);
		}
		sql.append(") u "); 
		sql.append("JOIN user "); 
		sql.append("ON u.id = user.id "); 
		if(sortStatus == SortStatus.SUBSCRIBETIME.toIntValue()){
			sql.append("  ORDER BY u.subscribeTime DESC");
		}else if (sortStatus == SortStatus.MONEY.toIntValue()){
			sql.append("  ORDER BY u.comsumeMoney DESC  "); //-- 业绩
		}
		return queryForList(sql.toString(), UserRelationshipsBO.class, param);
	}
	
	@Override
	public int getUserRelationshipsCount(String Id,String params) {
		StringBuilder sql = new StringBuilder();
		StatementParameter param = new StatementParameter();
		
		sql.append("select count(*) from user where is_delete=? and parent_id=?");
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(Id);
		
		if(StringUtils.isNotBlank(params)){
			sql.append(" and concat(IFNULL(name, ''), nick_name,IFNULL(mobile_phone, '')) LIKE CONCAT('%',?,'%')");
			param.setString(params);
		}
		
		return queryForInt(sql.toString(), param);
	}


	@Override
	public UserTakeCashBO getUserTakeCash(String id) {
		StringBuilder sql = new StringBuilder();
		StatementParameter param=new StatementParameter();
		
		sql.append("select ");
		sql.append("(select count(*) from rebate_info where user_id=? and status=?) 'wait_orders_goods_count',");
		sql.append("(select count(*) from rebate_info where user_id=? and status=?) 'free_orders_goods_count',");
		sql.append("(select sum(rebate_price) from rebate_info where user_id=?) 'free_orders_goods_money',");
		sql.append("withdrawal_money 'wait_take_cash_money' from user_config where user_id=?");
		
		param.setString(id);
		param.setInt(Status.ACTIVE.toIntStatus());
		param.setString(id);
		param.setInt(Status.INACTIVE.toIntStatus());
		param.setString(id);
		param.setString(id);
		
		return query(sql.toString(), UserTakeCashBO.class, param);
	}
	
	@Override
	public User findByMobilePhone(String phone){
		StringBuffer sql=new StringBuffer();
		sql.append(" select * from `user`");
		sql.append(" where mobile_phone=?");
		sql.append(" and is_delete=?");
		
		StatementParameter param=new StatementParameter();
		param.setString(phone);
		param.setInt(IsDelete.NO.toIntValue());
		
		return query(sql.toString(), User.class, param);
	}
	
	@Override
	public List<User> findByShopId(String shopId){
		StringBuffer sql=new StringBuffer();
		sql.append(" select * from `user`");
		sql.append(" where shop_id=?");
		sql.append(" and is_shop=?");
		sql.append(" and is_delete=?");
		sql.append(" order by bind_time asc");
		
		StatementParameter param=new StatementParameter();
		param.setString(shopId);
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		
		return queryForList(sql.toString(), User.class, param);
	}
	
	@Override
	public int findByShopIdCount(String shopId){
		StringBuffer sql=new StringBuffer();
		sql.append(" select count(*) from `user`");
		sql.append(" where shop_id=?");
		sql.append(" and is_shop=?");
		sql.append(" and is_delete=?");
		
		StatementParameter param=new StatementParameter();
		param.setString(shopId);
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		
		return super.queryForInt(sql.toString(), param);
	}
	
	@Override
	public User findByShopIdIsMain(String shopId){
		StringBuffer sql=new StringBuffer();
		sql.append(" select * from `user`");
		sql.append(" where shop_id=?");
		sql.append(" and is_shop=?");
		sql.append(" and is_main_promotion=?");
		sql.append(" and is_delete=?");
		
		StatementParameter param=new StatementParameter();
		param.setString(shopId);
		param.setInt(Is.YES.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		
		return super.query(sql.toString(), User.class, param);
	}
	
	@Override
	public List<User> findByUser(User user){
		StringBuffer sql=new StringBuffer();
		sql.append(" select * from `user`");
		sql.append(" where 1=1");
		
		StatementParameter param=new StatementParameter();
		if(StringUtils.isNotBlank(user.getParentId())){
			sql.append(" and parent_id=?");
			param.setString(user.getParentId());
		}
		if(user.getCreatedTime()!=null){
			sql.append(" and created_time >=?");
			param.setDate(user.getCreatedTime());
		}
		
		return queryForList(sql.toString(), User.class,param);
	}

	@Override
	public User findUserIsMain(String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from `user` ");
		sql.append(" where shop_id = ? and is_main_promotion = ?");
		StatementParameter params = new StatementParameter();
		params.setString(shopId);
		params.setInt(Is.YES.toIntValue());
		return super.query(sql.toString(),User.class,params);
	}

	@Override
	public List<User> findUserList() {
		String sql = "select * from `user` where created_time > '2018-08-25 00:00:00' and avatar_url <> ''";
		return super.queryForList(sql,User.class);
	}

	@Override
	public List<UserBO> queryUserList(UserBO userBO,Integer pageNumber, Integer maxPageRow) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		sql.append("select * ");
		sql.append("from ( ");
		sql.append("	select ");
		sql.append("		u.id,u.avatar_url,u.nick_name,u.sex,u.mobile_phone,u.country,u.province,u.city, ");
		sql.append("		ifnull(uc.pile_sell_money,0) pile_sell_money,ifnull(uc.pile_income_money,0) pile_income_money, ");
		sql.append("		ifnull(uc.pile_rebate_money,0) pile_rebate_money,uc.withdrawal_money,uc.lege_integral,uc.pile_lege_integral,");
		sql.append("		count(fc.parent_id) first_children_count,count(sc.parent_id) second_children_count, ");
		sql.append("		count(fc.parent_id)+count(sc.parent_id) membership ");
		sql.append("	from user u ");
		sql.append("	inner join user_config uc on u.id=uc.user_id ");
		sql.append("	left join ( ");
		sql.append("		select ");
		sql.append("			id,parent_id ");
		sql.append("		from user ");
		sql.append("		where is_delete=? ");
		param.setInt(IsDelete.NO.toIntValue());
		sql.append("	) fc on u.id=fc.parent_id ");
		sql.append("	left join ( ");
		sql.append("		select ");
		sql.append("			id,parent_id ");
		sql.append("		from user ");
		sql.append("		where is_delete=? ");
		param.setInt(IsDelete.NO.toIntValue());
		sql.append("	) sc on fc.id=sc.parent_id ");
		sql.append("	where u.is_delete=? ");
		param.setInt(IsDelete.NO.toIntValue());
		if(StringUtils.isNoneBlank(userBO.getNickName())){
			sql.append("and u.nick_name like ? ");
			param.setString("%"+userBO.getNickName()+"%");
		}
		if(StringUtils.isNoneBlank(userBO.getMobilePhone())){
			sql.append("and u.mobile_phone=? ");
			param.setString(userBO.getMobilePhone());
		}
		if(userBO.getSex()!=null){
			sql.append("and u.sex=? ");
			param.setInt(userBO.getSex());
		}
		if(StringUtils.isNoneBlank(userBO.getParentId())){
			sql.append("and u.parent_id in ( ");
			sql.append("	select ");
			if(userBO.getLevelSearch()==1){
				sql.append("parent_id");
			}
			if(userBO.getLevelSearch()==2){
				sql.append("id");
			}
			sql.append("	from user");
			sql.append("	where is_delete=? and parent_id=?");
			param.setInt(IsDelete.NO.toIntValue());
			param.setString(userBO.getParentId());
			sql.append(")");
		}
		sql.append("group by id,avatar_url,nick_name,sex,mobile_phone,country,province,city,pile_sell_money,pile_income_money,pile_rebate_money,withdrawal_money,lege_integral,pile_lege_integral ");
		sql.append(") ucc ");
		sql.append("where 1=1 ");
		if(userBO.getMembership()!=null){
			sql.append("and ucc.membership>=? ");
			param.setInt(userBO.getMembership());
		}
		sql.append("order by ucc.membership desc");

		return super.queryForPagingList(sql.toString(), UserBO.class, param, pageNumber,maxPageRow);
	}

	@Override
	public Integer getUserCount(UserBO userBO) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		sql.append("select count(ucc.id) ");
		sql.append("from (");
		sql.append("	select ");
		sql.append("		u.id,u.avatar_url,u.nick_name,u.sex,u.mobile_phone,u.country,u.province,u.city, ");
		sql.append("		ifnull(uc.pile_sell_money,0) pile_sell_money,ifnull(uc.pile_income_money,0) pile_income_money, ");
		sql.append("		ifnull(uc.pile_rebate_money,0) pile_rebate_money,uc.withdrawal_money,uc.lege_integral,uc.pile_lege_integral,");
		sql.append("		count(fc.parent_id) first_children_count,count(sc.parent_id) second_children_count, ");
		sql.append("		count(fc.parent_id)+count(sc.parent_id) membership ");
		sql.append("	from user u ");
		sql.append("	inner join user_config uc on u.id=uc.user_id ");
		sql.append("	left join ( ");
		sql.append("		select ");
		sql.append("			id,parent_id ");
		sql.append("		from user ");
		sql.append("		where is_delete=? ");
		param.setInt(IsDelete.NO.toIntValue());
		sql.append("	) fc on u.id=fc.parent_id ");
		sql.append("	left join ( ");
		sql.append("		select ");
		sql.append("			id,parent_id ");
		sql.append("		from user ");
		sql.append("		where is_delete=? ");
		param.setInt(IsDelete.NO.toIntValue());
		sql.append("	) sc on fc.id=sc.parent_id ");
		sql.append("	where u.is_delete=? ");
		param.setInt(IsDelete.NO.toIntValue());
		if(StringUtils.isNoneBlank(userBO.getNickName())){
			sql.append("and u.nick_name like ? ");
			param.setString("%"+userBO.getNickName()+"%");
		}
		if(StringUtils.isNoneBlank(userBO.getMobilePhone())){
			sql.append("and u.mobile_phone=? ");
			param.setString(userBO.getMobilePhone());
		}
		if(userBO.getSex()!=null){
			sql.append("and u.sex=? ");
			param.setInt(userBO.getSex());
		}
		if(StringUtils.isNoneBlank(userBO.getParentId())){
			sql.append("and u.parent_id in ( ");
			sql.append("	select ");
			if(userBO.getLevelSearch()==1){
				sql.append("parent_id");
			}
			if(userBO.getLevelSearch()==2){
				sql.append("id");
			}
			sql.append("	from user");
			sql.append("	where is_delete=? and parent_id=?");
			param.setInt(IsDelete.NO.toIntValue());
			param.setString(userBO.getParentId());
			sql.append(")");
		}
		sql.append("group by u.id,nick_name,sex,mobile_phone,country,province,city,pile_sell_money,pile_income_money,pile_rebate_money,withdrawal_money,lege_integral,pile_lege_integral ");
		sql.append(") ucc ");
		sql.append("where 1=1 ");
		if(userBO.getMembership()!=null){
			sql.append("and ucc.membership>=? ");
			param.setInt(userBO.getMembership());
		}
		return super.queryForInt(sql.toString(), param);
	}
}
